﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ELS.User
{
    class GroupDataAccess
    {
        string connectionString = UserCommon.connectionString;
        internal DataTable SelectAllGroups()
        {
            DataTable dtAllGroups = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select [Group].ID as GROUP_ID,
	                                                        [Group].NAME as GROUPNAME from [Group]", sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllGroups);
                    return dtAllGroups;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Selects the Groups by username.
        /// </summary>
        /// <param name="username">The username.</param>
        /// <returns>DataTable.</returns>
        internal DataTable SelectGroupsByUsername(string username)
        {
            DataTable dtUsers = new DataTable(); ;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select [User].ID as USER_USERID,
	                                                        [User].USERNAME,
	                                                        [User].PASSWORD,
	                                                        [User].FULLNAME,
	                                                        [User].EMAIL,
	                                                        [User].ADDRESS,
	                                                        [User].PHONE,
	                                                        [User].ORGANIZATION,
	                                                        UserGroup.ID as USERGROUP_ID,
	                                                        UserGroup.USER_ID as USERGROUP_USERID,
	                                                        UserGroup.GROUP_ID as USERGROUP_GROUPID,
	                                                        [Group].ID as GROUP_ID,
	                                                        [Group].NAME as GROUPNAME,
	                                                        [Group].DESCRIPTION as GROUP_DESCRIPTION
                                                            from [User],UserGroup,[Group] 
                                                            where [User].ID = UserGroup.USER_ID
                                                            and UserGroup.GROUP_ID = [Group].ID
                                                            and [User].USERNAME =  @username ", sqlConnection);
                    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtUsers);
                    return dtUsers;
                }
                catch (Exception e)
                {
                    return dtUsers;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable SelectGroupById(int groupId)
        {
            DataTable dtAllGroups = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select [Group].ID as GROUP_ID,
	                                                        [Group].NAME as GROUPNAME,
                                                            [Group].DESCRIPTION
                                                            from [Group]
                                                             WHERE [Group].ID = @id", sqlConnection);
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = groupId;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllGroups);
                    return dtAllGroups;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable SelectGroupByName(string groupName)
        {
            DataTable dtAllGroups = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select [Group].ID as GROUP_ID,
	                                                        [Group].NAME as GROUPNAME,
                                                            [Group].DESCRIPTION
                                                            from [Group]
                                                             WHERE [Group].NAME = @name", sqlConnection);
                    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = groupName;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllGroups);
                    return dtAllGroups;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int UpdateGroup(int groupId, string grpName, string grpDes)
        {
            int rowUpdate = 0 ;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"UPDATE [Group] SET
	                                                        [Group].NAME = @name,
                                                            [Group].DESCRIPTION = @desc                                                            
                                                             WHERE [Group].ID = @id", sqlConnection);
                    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = grpName;
                    cmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = grpDes;
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = groupId;
                    rowUpdate = cmd.ExecuteNonQuery();
                    return rowUpdate;
                }
                catch (Exception e)
                {
                    return rowUpdate;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int InsertGroup(string grpName, string grpDes)
        {
            int rowInsert = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"INSERT INTO [Group] (NAME,DESCRIPTION) VALUES
	                                                        (@name,
                                                             @desc)", sqlConnection);
                    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = grpName;
                    cmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = grpDes;
                    rowInsert = cmd.ExecuteNonQuery();
                    return rowInsert;
                }
                catch (Exception e)
                {
                    return rowInsert;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int DeleteGroup(string listGroup)
        {
            int rowDelete = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"DELETE FROM [Group] WHERE [Group].ID IN ("+listGroup+")", sqlConnection);                   
                    rowDelete = cmd.ExecuteNonQuery();
                    return rowDelete;
                }
                catch (Exception e)
                {
                    return rowDelete;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }
    }
}
